package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.OrderDetailViewModel;
import model.OrderViewModel;

public class Orders {
	public Orders(){}
	
	public List<OrderViewModel> getOrders(int status){
		List<OrderViewModel> list=new ArrayList<OrderViewModel>();
		Connection con=new Connect().getConnection();
		String sql;
		if(status == -1){
			sql = "Select * from Orders";
		}else{
			sql = "Select * from Orders where status ='" + status + "'";
		}
		try{
			PreparedStatement state =con.prepareStatement(sql);
			ResultSet rs=state.executeQuery();
			ResultSet rs1 = null;
			while(rs.next())
			{
				OrderViewModel model = new OrderViewModel(
						rs.getString("id"),
						rs.getString("date"),
						rs.getString("taken_date"),
						rs.getInt("user_id"),
						rs.getInt("status"),
						"");
				String sql1 = "select name from UserProfile where user_id='" + model.getUserId() + "'";
				state = con.prepareStatement(sql1);
				rs1 = state.executeQuery();
				while(rs1.next()){
					model.setCustomerName(rs1.getString("name"));
				}
				list.add(model);
			}
			con.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<OrderDetailViewModel> getOrderDetails(String orderId, String productId){
		List<OrderDetailViewModel> list=new ArrayList<OrderDetailViewModel>();
		Connection con=new Connect().getConnection();
		String sql;
		if(productId != null){
			sql = "Select * from DetailOrder where product_id ='" + productId + "'";
		}
		else if(orderId != null){
			sql = "Select * from DetailOrder where order_id ='" + orderId + "'";
		}else{
			return null;
		}
		
		try{
			PreparedStatement state =con.prepareStatement(sql);
			ResultSet rs = state.executeQuery();
			while(rs.next())
			{
				OrderDetailViewModel model = new OrderDetailViewModel(
						rs.getString("order_id"),
						rs.getString("product_id"),
						rs.getInt("quantities"),
						"");
				String sql1 = "select name from Products where id='" + model.getProductId() + "'";
				state = con.prepareStatement(sql1);
				ResultSet rs1 = state.executeQuery();
				while(rs1.next()){
					model.setProductName(rs1.getString("name"));
				}
				list.add(model);
			}
			con.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	} 
	
	public void DeleteOrder(String orderId) {
		Connection con = new Connect().getConnection();
		String sql1 = "DELETE FROM ORDER WHERE ID = '" + orderId + "'";
		String sql2 = "DELETE FROM DETAILORDER WHERE ORDER_ID = '" + orderId + "'";
		try {
			PreparedStatement state1 = con.prepareStatement(sql1);
			PreparedStatement state2 = con.prepareStatement(sql1);
			state1.executeUpdate();
			state2.executeUpdate();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
